﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Windows.Storage;
using System.Reflection;
namespace AppCommon
{
    /// <summary>
    /// 数据库访问类
    /// </summary>
    /// <typeparam name="T">对象类型</typeparam>
    public class DBManager<T> where T : new()
    {
        private static string path = "";
        private static DBManager<T> manager = new DBManager<T>();


        private DBManager()
        {

        }

        public async static Task<DBManager<T>> GetInstance()
        {
            var root = Windows.Storage.ApplicationData.Current.LocalFolder;
            var path1 = await root.CreateFolderAsync("sql", CreationCollisionOption.OpenIfExists);
            var filename = await path1.CreateFileAsync("db.db3", CreationCollisionOption.OpenIfExists);
            path = filename.Path;
            return manager;
        }

        public async Task<List<T>> GetList()
        {
            SQLite.SQLiteAsyncConnection conn = new SQLite.SQLiteAsyncConnection(path);
            var query = await conn.Table<T>().ToListAsync();

            return query;
        }

        public async Task<List<T>> GetListByPage(int pageSize, int nowPage)
        {
            SQLite.SQLiteAsyncConnection conn = new SQLite.SQLiteAsyncConnection(path);
            T t = new T();
            string tableName = t.ToString().Substring(t.ToString().LastIndexOf(".") + 1);
            string sql = "select * from " + tableName + " limit " + (nowPage - 1) * pageSize + "," + pageSize;
            var query = await conn.QueryAsync<T>(sql);
            return query;
        }

        public async Task<List<T>> GetListByPageAndKeys(int pageSize, int nowPage, KeyValuePair<string, string>[] keys)
        {
            SQLite.SQLiteAsyncConnection conn = new SQLite.SQLiteAsyncConnection(path);
            T t = new T();
            string tableName = t.ToString().Substring(t.ToString().LastIndexOf(".") + 1);
            string where = "";
            foreach (KeyValuePair<string, string> k in keys)
            {
                where += k.Key + "='" + k.Value + "' and ";
            }
            where = where.Substring(0, where.Length - 4);

            string sql = "select * from " + tableName + " where " + where + " limit " + (nowPage - 1) * pageSize + "," + pageSize;
            var query = await conn.QueryAsync<T>(sql);
            return query;
        }

        public async Task<T> GetModel(int t)
        {
            SQLite.SQLiteAsyncConnection conn = new SQLite.SQLiteAsyncConnection(path);
            var query = await conn.FindAsync<T>(t);
            return query;
        }

        public async Task<int> CreateTable()
        {
            SQLite.SQLiteAsyncConnection conn = new SQLite.SQLiteAsyncConnection(path);
            await conn.CreateTableAsync<T>();
            return 1;
        }

        public async Task<bool> IsExist(KeyValuePair<string, string>[] keys)
        {
            SQLite.SQLiteAsyncConnection conn = new SQLite.SQLiteAsyncConnection(path);
            T t = new T();
            string tableName = t.ToString().Substring(t.ToString().LastIndexOf(".") + 1);
            string where = "";
            foreach (KeyValuePair<string, string> k in keys)
            {
                where += k.Key + "='" + k.Value + "' and ";
            }
            where = where.Substring(0, where.Length - 4);

            string sql = "select count(*) from " + tableName + " where " + where;
            int resultValue = await conn.ExecuteScalarAsync<int>(sql);

            if (resultValue > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public async Task<int> Edit(T t)
        {
            SQLite.SQLiteAsyncConnection conn = new SQLite.SQLiteAsyncConnection(path);
            await conn.UpdateAsync(t);
            return 1;
        }

        public async Task<int> DeleteSome(List<T> ts)
        {
            foreach (T t in ts)
            {
                await Delete(t);
            }
            return 1;
        }

        public async Task<int> Delete(T t)
        {
            SQLite.SQLiteAsyncConnection conn = new SQLite.SQLiteAsyncConnection(path);
            await conn.DeleteAsync(t);
            return 1;
        }

        public async Task<int> Insert(T t)
        {
            SQLite.SQLiteAsyncConnection conn = new SQLite.SQLiteAsyncConnection(path);
            await conn.InsertAsync(t);
            return 1;
        }
    }
}
